package com.app.business.service;

import com.app.business.model.ShopHouse;
import com.app.system.jpa.JpaBaseDao;
import com.app.system.utils.CollectionUtils;
import com.app.system.utils.dataType.StringUtils;
import com.google.common.collect.Maps;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Service;

import javax.persistence.Query;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by llf on 2024/05/08.
 * 公示业务层
 * 店铺公示使用
 * @author llf
 */
//Test
    
@Service
public class ShowShopService extends JpaBaseDao<ShopHouse, Integer> {
    /**
     * 通过安置项目、楼号、建筑面积得到所分配的houseList[houseNumList?]
     * 锦溪二期店面使用
     * @param azProjectId
     * @param houseStatus
     * @return
     */
    public Map<String, List> shopHouseMapFoJx(Integer azProjectId, Integer houseStatus) {
        StringBuffer hql = new StringBuffer("FROM ShopHouse h WHERE h.azProject.id = :azProjectId ");
//        StringBuffer hql = new StringBuffer("FROM ShopHouse h WHERE h.azProject.id IN (" + azProjectIdStr + ") ");
        if (houseStatus != null) {
            hql.append(" AND h.houseStatus = :houseStatus");
        }
        hql.append(" ORDER BY h.buildNum,h.floor,h.houseNum");

//        map对象存储查询参数
        Map<String, Object> map = Maps.newLinkedHashMap();
        map.put("azProjectId", azProjectId);
        if (houseStatus != null) {
            map.put("houseStatus", houseStatus);
        }
//        findHql执行查询,返回List<House> 类型的对象,房源列表
        List<ShopHouse> mapList = this.findHql(hql.toString(), map);
        List houseList = new ArrayList<>();
//        List houseList;

        Map<String, List> resultMap = Maps.newHashMap();
        String buildNum = null;
//        查询结果不为空
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (ShopHouse sph : mapList) {
                buildNum = sph.getBuildNum();//获取每个房屋的楼栋号 buildNum
                //检查 resultMap 中是否已存在以当前楼栋号为键的列表，如果不存在，则创建一个新的列表；如果已存在，则获取该列表。
                houseList = resultMap.get(buildNum) == null ? new ArrayList() : resultMap.get(buildNum);
                houseList.add(sph);//将当前房屋对象 sph 添加到对应楼栋号的列表中。
                resultMap.put(buildNum, houseList);//将更新后的列表放入 resultMap 中，以楼栋号为键。
            }
        }
        // 遍历Map中的每个键值对
        resultMap.forEach((key, value) -> {
            // 使用Stream API遍历列表并打印内容
        });
        return resultMap;
    }

    /**
     * 通过征收项目、安置项目、楼号、房源类型\、是否保障区、建筑面积得到所分配的houseNumList
     *
     * @param azProjectId
     * @return
     */
    //List<String>返回一个字符串列表，该列表包含楼栋号。
    public List<String> buildNumListFoJx(Integer azProjectId,Integer houseStatus, String buildNumStr) {
//        StringBuffer sql = new StringBuffer("SELECT FLOOR(h.build_area+h.pool_area) AS area,h.build_num AS buildNum FROM shop_house h WHERE h.az_project_id = :azProjectId ");
        StringBuffer sql = new StringBuffer("SELECT DISTINCT h.build_num AS buildNum FROM shop_house h WHERE h.az_project_id = :azProjectId ");

//        sql.append("  and h.id in (SELECT DISTINCT zph.house_id FROM zs_project_house zph WHERE zph.zs_project_id in (" + zsProjectId + ")) ");
        if (houseStatus != null) {
            sql.append(" AND h.house_Status = :houseStatus");
        }
        if (StringUtils.isNotEmpty(buildNumStr)) {
            sql.append(" AND h.build_num in (" + buildNumStr + ") ");
        }
//        sql.append(" GROUP BY h.build_num ");
//        sql.append(" ORDER BY CONVERT(h.build_num,SIGNED),h.house_num ");
        //方法内部构建了一个 SQL 查询语句，根据输入的参数动态拼接查询条件，然后执行查询操作，从数据库中获取满足条件的楼栋号列表。
        //查询结果以字符串列表的形式返回，每个字符串表示一个楼栋号。
        Query query = this.entityManager.createNativeQuery(sql.toString());
        query.setParameter("azProjectId", azProjectId);//使用 query.setParameter() 方法设置查询参数
        if (houseStatus != null) {
            query.setParameter("houseStatus", houseStatus);
        }
        //执行查询
        //unwrap(org.hibernate.SQLQuery.class) 方法将查询对象转换为 Hibernate 的 SQL 查询对象
        //.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP) 设置结果转换器，将查询结果转换为 Map 的形式，其中键是字段的别名，值是字段的值。
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<String> buildNumList = new ArrayList<>();//存储楼号用
        String buildNum = null;
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                buildNum = map.get("buildNum").toString();
                buildNumList.add(buildNum);
            }
        }
        //打印buildNumList
        return buildNumList;//返回List<String>,结果包含查询结果中的所有楼号
    }

//    Temp
/*    public List<ShopHouse> shopHouseListFoJx(Integer azProjectId, String buildNum, Integer houseStatus) {
        StringBuilder hql = new StringBuilder("FROM ShopHouse h WHERE h.azProject.id = :azProjectId ");

        if (houseStatus != null) {
            hql.append(" AND h.houseStatus = :houseStatus");
        }
        if (StringUtils.isNotEmpty(buildNum)) {
            hql.append(" AND h.buildNum = :buildNum");
        }
        hql.append(" ORDER BY h.buildNum, h.floor, h.houseNum");

        Map<String, Object> params = new HashMap<>();
        params.put("azProjectId", Arrays.asList(azProjectId.split(",")));
        if (houseStatus != null) {
            params.put("houseStatus", houseStatus);
        }
        if (StringUtils.isNotEmpty(buildNum)) {
            params.put("buildNum", buildNum);
        }

        return findHql(hql.toString(), params);
    }

    *//**
     * 通过安置项目、楼号、房源类型\、是否保障区、建筑面积得到所分配的houseNumList
     *
     * @param azProjectIdStr
     * @param houseStatus
     * @param buildNumStr
     * @return
     *//*
    public List<String> buildNumListFoJx(String azProjectIdStr, Integer houseStatus, String buildNumStr) {
        StringBuilder sql = new StringBuilder("SELECT DISTINCT h.buildNum AS buildNum ");
        sql.append("FROM ShopHouse h WHERE h.azProject.id IN (:azProjectIds) ");

        if (houseStatus != null) {
            sql.append(" AND h.houseStatus = :houseStatus");
        }
        if (StringUtils.isNotBlank(buildNumStr)) {
            sql.append(" AND h.buildNum IN (:buildNums)");
        }
        sql.append(" ORDER BY h.buildNum");

        Query query = entityManager.createQuery(sql.toString());
        query.setParameter("azProjectIds", Arrays.asList(azProjectIdStr.split(",")));
        if (houseStatus != null) {
            query.setParameter("houseStatus", houseStatus);
        }
        if (StringUtils.isNotBlank(buildNumStr)) {
            query.setParameter("buildNums", Arrays.asList(buildNumStr.split(",")));
        }

        return query.getResultList();
    }*/
}